package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.sun.org.apache.bcel.internal.generic.ARRAYLENGTH;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class OilOpenRateDAO {
	private MyConnectDSDAO mcdsDAO = new MyConnectDSDAO();
	private MyConnectDSTransDAO mcdstDAO = new MyConnectDSTransDAO();
	private IBaseDAO baseDAO;

	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}

	public OilOpenRateDAO(){
		
	}
	/**
	 * 查询JPP设备信息
	 **/
	public List<Map<String, Object>> queryPGNVclInfoList(String workTime) {
		String strSQL = "select OSO_VclID as VehicleID from OilSwitchOpen_"+workTime.replaceAll("-", "");
		return this.getBaseDAO().execQuery(strSQL.toString(), new Object[]{});
	}

	/**
	 * 校验指定日期的“油门开度信息表”是否存在，如果不存在，则进行创建
	 **/
	public boolean checkCreateOilOpenRate(String workTime,String isbakTable) {
		boolean flag = false;
		String sql = "Select count(id) as counts FROM SysObjects Where name = 'OilSwitchOpen_"+workTime.replaceAll("-", "")+"' and xtype = 'U'";
		if(Integer.parseInt(MapGetter.getString(this.getBaseDAO().execQueryOne(sql, new Object[]{}),"counts"))>0){
			flag = true;
		}else{
			sql = "create table dbo.OilSwitchOpen_"+workTime.replaceAll("-", "")+"(OSO_ID	decimal(18, 0) identity(1,1) primary key not null,"
		        +"OSO_VclID	decimal(18,0) not  null,OSO_Date	datetime null,"
				+"OSO_AllCounts	int  null,OSO_ValidCounts	int null,"
		        +"OSO_Zero	int null,OSO_UnderTen	int null,OSO_TenToTwe	int null,"
				+"OSO_TweToThi	int null,OSO_ThiToFor	int null,OSO_ForToFif	int null,"
		        +"OSO_FifToSix	int null,OSO_SixToSev	int null,"
				+"OSS_SevToEig	int null,OSO_EigToNin	int null,OSO_NinToHun	int null);";
			if(this.mcdsDAO.execUpdate(sql, new Object[]{})>-1){
				flag = true;
			}
		}
		if(isbakTable.equals("1")){
			sql = "create table dbo.OilSwitchOpen_"+workTime.replaceAll("-", "")+"(OSO_ID	decimal(18, 0) identity(1,1) primary key not null,"
			        +"OSO_VclID	decimal(18,0) not  null,OSO_Date	datetime null,"
					+"OSO_AllCounts	int  null,OSO_ValidCounts	int null,"
			        +"OSO_Zero	int null,OSO_UnderTen	int null,OSO_TenToTwe	int null,"
					+"OSO_TweToThi	int null,OSO_ThiToFor	int null,OSO_ForToFif	int null,"
			        +"OSO_FifToSix	int null,OSO_SixToSev	int null,"
					+"OSS_SevToEig	int null,OSO_EigToNin	int null,OSO_NinToHun	int null);";
			MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
			int bakTable = mucdd.execUpdate(sql, new Object[]{});
			if(bakTable<0){
				System.out.println("油门开度创建备份表失败");
			}
		}
		return flag;	
	}
	/**
	 * 新增所有有油门开度信息的所有JPP设备的总条数
	 **/
	public boolean inertOilOpenAllCounts(String workTime,String softVers,String isbakTable,Map<String,Object> param) {
		String sql = "";
		String linkDataBase = ""; 
		if(null != param){
			linkDataBase = "openrowset('SQLOLEDB','"+param.get("dataBaseIp")+"';'"+param.get("user")+"';'"+param.get("password")+"',"+param.get("MsgPDataBase")+".dbo.Msg_Pgn_"+workTime.replaceAll("-", "")+")";
			sql = "insert into OilSwitchOpen_"+workTime.replaceAll("-", "")+"(OSO_VclID,OSO_Date,OSO_AllCounts) " +
					" select mp.MsgP_Vcl_ID,'"+workTime+"' as workDate , count(mp.MsgP_ID) as counts " +
					" from V_VehicleInfo vv inner join "+linkDataBase+" mp on mp.MsgP_Vcl_ID = vv.VehicleID "+
					" where mp.MsgP_SysPIP_ID6=1 and UsefulLife>=GETDATE() and Tmnl_SoftEdition<>''and Tmnl_SoftEdition is not null and Tmnl_SoftEdition in ("+softVers+")" +
					" group by MsgP_Vcl_ID";
		}else{
			sql= "insert into OilSwitchOpen_"+workTime.replaceAll("-", "")+"(OSO_VclID,OSO_Date,OSO_AllCounts) " +
					" select mp.MsgP_Vcl_ID,'"+workTime+"' as workDate , count(mp.MsgP_ID) as counts " +
					" from V_VehicleInfo vv inner join "+GlobalDBVar.getDBFullName_Conn("Msg_Pgn_"+workTime.replaceAll("-", ""), "4", workTime+" 00:00:00")+" mp on mp.MsgP_Vcl_ID = vv.VehicleID "+
		            " where mp.MsgP_SysPIP_ID6=1 and UsefulLife>=GETDATE() and Tmnl_SoftEdition<>''and Tmnl_SoftEdition is not null and Tmnl_SoftEdition in ("+softVers+")" +
		            " group by MsgP_Vcl_ID";
		}
		
		System.out.println(sql);
		int num = this.mcdsDAO.execInsert(sql, new Object[]{});
		if(isbakTable.equals("1")){
			MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
			int bakTable = mucdd.execInsert(sql, new Object[]{});
			if(bakTable<0){
				System.out.println("备份所有有油门开度信息的所有JPP设备的总条数失败");
			}
		}
		System.out.println("新增所有有油门开度信息的所有JPP设备的总条数:"+num);
		return num>0?true:false;
	}
	//手动统计前先删除数据
	public void deleteOilOpen(String workTime) {
		String sql = "delete OilSwitchOpen_"+workTime.replaceAll("-", "");
		baseDAO.execUpdate(sql, new Object[]{});
		
	}

	public List<Map<String, Object>> getOilOpenCount(List<String> list) {
		String sql  = "";
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		for(String day : list){
			sql += "select count(1) as cont,'"+day+"' as workday from OilSwitchOpen_"+day.replaceAll("-", "")+" where OSO_AllCounts>0";
			sql +=" union all ";
		}
		sql =sql.substring(0, sql.length()-10);
		result = baseDAO.execQuery(sql, new Object[]{});
		return result;
	}

	public int getExeOilOpenCount(String isExectue) {
		String sql  = "select count(1) as cont from OilSwitchOpen_"+isExectue.replaceAll("-", "")+" where OSO_AllCounts>0";
		Map<String,Object> map = baseDAO.execQueryOne(sql, new Object[]{});
		String result = map.get("cont").toString();
		return Integer.parseInt(result);
	}
}
